﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

namespace StockAdmin.Dao
{
    public class FinancialDao
    {
        //Insert statement
        public void Insert(String p1, String p2, String p3, String p4, String p5)
        {
            // INSERT INTO `FinancialReport`(`stock_id`, `value`, `quarter`, `year`, `type`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6])

            if ("".Equals(p3))
            {
                p3 = null;
            }

            if ("".Equals(p4))
            {
                p4 = null;
            }




            string query = "INSERT INTO FinancialReport (stock_id, value,quarter,year,type ) VALUES('" + p1 + "', " + p2 + ", '" + p3 + "', " + p4 + ",'" + p5 + "') ";

            //open connection
            if (SqlHeper.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, SqlHeper.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                SqlHeper.CloseConnection();
            }
        }

        //Insert statement
        public void InsertOverView(String p1, String p2)
        {

            string query = "INSERT INTO Overview (stock_id, published_time,description) VALUES('" + p1 + "', " + "CURDATE()" + ", '" + p2 + "') ";

            //open connection
            if (SqlHeper.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, SqlHeper.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                SqlHeper.CloseConnection();
            }
        }


        //Insert statement
        public void InsertSnapShot(String p1, Decimal p2, int p3, Decimal p4, Double p5, int p6, int p7, Decimal p8, int p9, Decimal p10, Double p11, Double p12, Decimal p13)
        {

            string query = "INSERT INTO Snapshot (stock_id, lowest_in_52_weeks, created_time, updated_time, average_volume_in_10_days, dividend_yield, market_capitalization, outstanding_shares, listed_shares, highest_in_52_weeks, eps_4q, roa_trailing, pe_trailing, pb, roe_trailing) VALUES('" + p1 + "', " + p2 + " , " + "CURDATE()" + " , " + "CURDATE()" + " , " + p3 + " , " + p4 + ", " + p5 + ", " + p6 + ", " + p7 + ", " + p8 + ", " + p9 + ", " + p10 + ", " + p11 + ", " + p12 + ", " + p13 + " ) ";

            //open connection
            if (SqlHeper.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, SqlHeper.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                SqlHeper.CloseConnection();
            }
        }


        public String Select(String codeName )
        {
            string query = "SELECT * FROM Stock WHERE CODE = '" + codeName + "'";

            //Create a list to store the result
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();

            String abc = "";
            //open connection
            if (SqlHeper.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, SqlHeper.connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                ////Read the data and store them in the list
                while (dataReader.Read())
                {
                    abc = dataReader["id"] + "";
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                SqlHeper.CloseConnection();

                //return list to be displayed
                return abc;
            }
            else
            {
                return abc;
            }
        }
    }
}